// ~ Package Declaration
// ==================================================

package com.wms.data.dao.jdbc;

import java.beans.PropertyDescriptor;
import java.io.Serializable;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;

import com.wms.data.DataException;
import com.wms.data.PaginateModel;
import com.wms.data.dao.jdbc.core.SimpleJdbcTemplate;
import com.wms.data.pojo.Pojo;
import com.wms.utils.PojoUtil;
import com.wms.utils.lang.VSClassUtils;

// ~ Comments
// ==================================================

/**
 * 
 * Jdbc Dao super class.
 * 
 * <pre>
 * 歷史"��"
 * 2009-5-12 Timothy
 * 	新建檔案
 * </pre>
 * 
 * @author <pre>
 * SD
 * 	Timothy
 * PG
 * 
 * UT
 * 
 * MA
 * </pre>
 * @version $Rev$
 * 
 *          <p/>
 *          $Id$
 * 
 */
@Repository
public abstract class AbstractJdbcDao<P extends Pojo<PK>, PK extends Serializable>
		 implements JdbcDao<P, PK> {

	// ~ Static Fields
	// ==================================================

	// ~ Fields
	// ==================================================

	private final Log logger = LogFactory.getLog(this.getClass());

	private Class<P> pojoClass;
	private Class<PK> pojoPKClass;

	/**
	 * Timothy, 2010-2-23 <br>
	 * 用于存放pojo的所有属"
	 */
	private final Map<String, String> pojoPropertiesNamesIgnoreCaseMap;

	@Autowired
	private SimpleJdbcTemplate simpleJdbcTemplate;

	/**
	 * 用于处理不同db的不同sql规格的状况，比如分页...
	 */
	private DatabaseType databaseType;

	private final List<String> primaryKey;
	private final List<String> tableColumns;
	private final List<String> tableColumnsExpectPrimaryKey;

	private final Map<String, String> columnsMapPojoProperty;

	private final String findByIdSql;
	private final String findAllSql;

	// "autoIncrement "true 时，"��重新定义此变数，"��不为 final
	private String createSql;
	private final String updateSql;
	private final String deleteSql;

	/**
	 * Timothy, 2010-2-23 <br>
	 * 是否为主键自动增"
	 */
	private boolean autoIncrement;

	// ~ Constructors
	// ==================================================

	public AbstractJdbcDao() {

		if (StringUtils.isEmpty(this.getActualTableName())) {
			throw new DataException(String.format("[%s]对应的表名不可为空！ ", this.getClass().getName()));
		}

		if ((this.getActualTableColumns() == null) || (this.getActualTableColumns().length == 0)) {
			throw new DataException(String.format("[%s]对应的栏位不可为空！ ", this.getClass().getName()));
		}

		this.pojoClass = this.findAssignablePojoClass(this.getClass());
		this.pojoPKClass = this.findAssignablePojoPKClass(this.pojoClass);
		this.pojoPropertiesNamesIgnoreCaseMap = this.getPojoPropertiesNamesIgnoreCaseMap();

		this.columnsMapPojoProperty = this.generateColumnsMapPojoProperty();
		Set<String> existColumns = this.columnsMapPojoProperty.keySet();

		this.primaryKey = this.retainColumns(this.getActualPrimaryKey(), existColumns);
		this.tableColumns = this.retainColumns(this.getActualTableColumns(), existColumns);
		this.tableColumnsExpectPrimaryKey = this.retainColumns(this
				.getActualTableColumnsExpectPrimaryKey(), existColumns);

		String whereSqlById = this.generateWhereSqlById();

		this.findAllSql = String.format(" SELECT %s,%s FROM %s ",
				StringUtils.join(this.getActualPrimaryKey(), ", "),
				StringUtils.join(this.getActualTableColumns(), ", "),
				this.getActualTableName());
		this.findByIdSql = this.findAllSql + whereSqlById;
		this.createSql = this.generateCreateSqlById();
		this.updateSql = this.generateUpdateSqlById(whereSqlById);
		this.deleteSql = String.format(" DELETE FROM %s %s ", this.getActualTableName(), whereSqlById);
	}

	// ~ Methods
	// ==================================================

	public String[] getColumns() {

		String[] columns = new String[getActualTableColumns() != null
				? getActualTableColumns().length
					: 0];

		if (getActualTableColumns() != null) {

			for (int i = 0; i < getActualTableColumns().length; i++) {

				columns[i] = StringUtils.remove(getActualTableColumns()[i].toLowerCase(), '_');
			}
		}

		return columns;
	}

	/**
	 * createDetails.
	 *
	 * <pre>
	 *	2012/4/19
	 * </pre>
	 *
	 * @see com.wms.data.dao.Dao#createDetails(java.lang.Object, java.lang.Object, java.lang.String[])
	 */
	public String createDetails(Object before, Object after, String[] columns, String tableName) {

		StringBuilder beforeDetails = new StringBuilder("Modify ")
				.append(tableName + ": \n")
					.append(tableName + " value of the affected data; \n");

		StringBuilder afterDetails = new StringBuilder("New value of the affected data; \n");

		SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss.SSS");

		try {

			for (PropertyDescriptor propertyDescriptor : BeanUtils.getPropertyDescriptors(this
					.getPojoClass())) {

				Object beforeValue = PropertyUtils.getSimpleProperty(before, propertyDescriptor.getName());
				Object afterValue = PropertyUtils.getSimpleProperty(after, propertyDescriptor.getName());

				if (ArrayUtils.contains(columns, propertyDescriptor.getName().toLowerCase())) {

					if (beforeValue != null) {

						if (!beforeValue.equals(afterValue)) {

							// if value is date, format date to (dd/MM/yyyy HH:mm:ss.SSS)
							if (beforeValue instanceof Date) {

								beforeDetails.append(propertyDescriptor.getName()
										+ " = "
										+ format.format(beforeValue)
										+ " \n");

								if (afterValue instanceof Date) {

									afterDetails.append(propertyDescriptor.getName()
											+ " = "
											+ format.format(afterValue)
											+ " \n");
								}
								else {

									afterDetails.append(propertyDescriptor.getName() + " = " + afterValue + " \n");
								}
							}

							// if value is not date, append value
							else {

								beforeDetails.append(propertyDescriptor.getName() + " = " + beforeValue + " \n");
								afterDetails.append(propertyDescriptor.getName() + " = " + afterValue + " \n");
							}
						}
					}
					else if (afterValue != null) {

						beforeDetails.append(propertyDescriptor.getName() + " = " + beforeValue + " \n");
						afterDetails.append(propertyDescriptor.getName() + " = " + afterValue + " \n");
					}
				}
			}
		}
		catch (Exception e) {

			logger.error(">>>>>>>>>>>>>>>>>>This is create audit Log Details error!!!>>>>>>>>>>>>>>>>>>>>");

			throw new RuntimeException(e.getMessage());
		}

		return beforeDetails.toString() + afterDetails.toString();
	}


	/*
	 * 2010-2-5 Timothy
	 * 
	 * @see com.vs.data.dao.Dao#findAll()
	 */
	public List<P> findAll() {

		return this.getSimpleJdbcTemplate().query(this.findAllSql, this.generateParameterizedRowMapper());
	}

	/**
	 * find all by district id.
	 *
	 * <pre>
	 *	find all by district id.
	 * </pre>
	 *
	 * @see com.wms.data.dao.Dao#findAllByDistrictRid(java.lang.Long)
	 */
	public List<P> findAllByDistrictRid(Long districtRid) {

		String strSql = findAllSql + "WHERE DISTRICT_RID = ?";

		return getSimpleJdbcTemplate().query(strSql, generateParameterizedRowMapper(), districtRid);
	}

	/*
	 * 2010-2-5 Timothy
	 * 
	 * @see com.vs.data.dao.Dao#findAll(com.vs.data.PaginateModel)
	 */
	public List<P> findAll(PaginateModel paginateModel) {

		return this.getSimpleJdbcTemplate().query(this.findAllSql,
				paginateModel,
				this.getDatabaseType(),
				this.generateParameterizedRowMapper());
	}

	/*
	 * 2010-2-5 Timothy
	 * 
	 * @see com.vs.data.dao.Dao#findById(java.io.Serializable)
	 */
	public P findById(PK id) {
		
		if (id == null) {
			
			return null;
		}
		
		this.primaryKeyCouldNotEmpty();

		P pojo = this.generatePojoInstance();

		pojo.setId(id);

		return this.findById(pojo);
	}

	/*
	 * 2010-2-20 Timothy
	 * 
	 * @see com.vs.data.dao.jdbc.JdbcDao#findById(com.vs.data.pojo.Pojo)
	 */
	public P findById(P pojo) {

		this.primaryKeyCouldNotEmpty();

		List<P> results = this.getSimpleJdbcTemplate().query(this.findByIdSql,
				this.generateParameterizedRowMapper(),
				pojo);

		if (results.isEmpty()) {

			return null;
		}

		return results.get(0);
	}

	/*
	 * 2010-3-5 Timothy
	 * 
	 * @see com.vs.data.dao.jdbc.JdbcDao#findByPojoFields(com.vs.data.pojo.Pojo)
	 */
	public List<P> findByPojoFields(P pojo) {

		return this.findByPojoFields(pojo, null);
	}

	/*
	 * 2010-3-5 Timothy
	 * 
	 * @see com.vs.data.dao.jdbc.JdbcDao#findByPojoFields(com.vs.data.pojo.Pojo,
	 * com.vs.data.PaginateModel)
	 */
	public List<P> findByPojoFields(P pojo, PaginateModel paginateModel) {

		List<String> whereConditions = new ArrayList<String>();

		// "处理 pk
		this.addWhereConditions(whereConditions, pojo, this.primaryKey);

		// "pk 的条件全部被加入时，仅作findById的查询即可！
		if ((this.primaryKey.size() > 0) && (this.primaryKey.size() == whereConditions.size())) {

			return this.returnPojosByPk(pojo, paginateModel);
		}

		// 再加入非 PK 的栏"
		this.addWhereConditions(whereConditions, pojo, this.tableColumnsExpectPrimaryKey);

		// "没有 任何条件加入，则"findAll 查询
		if (whereConditions.isEmpty()) {

			return (paginateModel == null) ? this.findAll() : this.findAll(paginateModel);
		}

		// 产生查询 sql
		String sql = String.format(" %s WHERE %s ",
				this.findAllSql,
				StringUtils.join(whereConditions, " AND "));

		this.logger.debug("Pojo field's sql >>> " + sql);

		// 进行查询
		if (paginateModel == null) {
			return this.getSimpleJdbcTemplate().query(sql, this.generateParameterizedRowMapper(), pojo);
		}
		else {

			return this.getSimpleJdbcTemplate().query(sql,
					paginateModel,
					this.getDatabaseType(),
					this.generateParameterizedRowMapper(),
					pojo);
		}
	}

	/**
	 * <pre>
	 * 2010-3-5 Timothy
	 * </pre>
	 * 
	 * @param pojo
	 * @param paginateModel
	 * @return
	 */
	private List<P> returnPojosByPk(P pojo, PaginateModel paginateModel) {

		List<P> results = new ArrayList<P>();

		P result = this.findById(pojo);

		if (result != null) {
			results.add(result);
		}

		if (paginateModel != null) {
			paginateModel.setTotalSize(results.size());
		}

		return results;
	};

	/**
	 * <pre>
	 * 2010-3-5 Timothy
	 * </pre>
	 * 
	 * @param whereConditions
	 * @param pojo
	 * @param pojoFieldName
	 * @param columnName
	 */
	private void addWhereConditions(List<String> whereConditions, P pojo, List<String> columnNames) {

		for (String columnName : columnNames) {

			String pojoFieldName = this.columnsMapPojoProperty.get(columnName);

			try {

				Object fieldValue = PropertyUtils.getProperty(pojo, pojoFieldName);

				if (fieldValue != null) {

					whereConditions.add(this.generatePojoFieldEqualsConditionSql(columnName));
				}
			}
			catch (Exception e) {

				throw new DataException(e);
			}
		}
	};

	/*
	 * 2010-2-5 Timothy
	 * 
	 * @see com.vs.data.dao.Dao#create(com.vs.data.pojo.Pojo)
	 */
	public void create(P pojo) {

		if (this.logger.isDebugEnabled()) {

			this.logger.debug(String.format("Insert %s's sql >>> [%s] \n %s's property is %s",
					this.getActualTableName(),
					this.createSql,
					this.getPojoClass(),
					pojo.toString()));
		}

		// 当为自动增长序列时，设定 pk
		if (this.autoIncrement) {

			PK id = this.getPrimaryKeySequenceNextValue();

			this.logger.debug(String.format("%s's PK value is >>> [%s].", this.getActualTableName(), id));

			pojo.setId(id);
		}

		this.getSimpleJdbcTemplate().update(this.createSql, pojo);
	}
	
	public PK save(P pojo) {
		
		if (this.logger.isDebugEnabled()) {

			this.logger.debug(String.format("Insert %s's sql >>> [%s] \n %s's property is %s",
					this.getActualTableName(),
					this.createSql,
					this.getPojoClass(),
					pojo.toString()));
		}

		// 当为自动增长序列时，设定 pk
		if (this.autoIncrement) {

			PK id = this.getPrimaryKeySequenceNextValue();

			this.logger.debug(String.format("%s's PK value is >>> [%s].", this.getActualTableName(), id));

			pojo.setId(id);
		}
		
		this.getSimpleJdbcTemplate().update(this.createSql, pojo);
		
		return pojo.getId();
	}

	public Long createReturnId(P pojo) {

		this.create(pojo);

		return this.getSimpleJdbcTemplate().queryForLong(String.format("SELECT MAX(%s) FROM %s",
				StringUtils.join(this.getActualPrimaryKey(), ", "),
				getActualTableName()));
	}

	/*
	 * 2010-2-5 Timothy
	 * 
	 * @see com.vs.data.dao.Dao#update(com.vs.data.pojo.Pojo)
	 */
	public void update(P pojo) {

		this.primaryKeyCouldNotEmpty();

		if (this.tableColumnsExpectPrimaryKey.isEmpty()) {
			throw new DataException(new UnsupportedOperationException(String.format("[%s]没有可被更新的非主键栏位，故不支援此操作",
					this.getActualTableName())));
		}

		if (this.logger.isDebugEnabled()) {

			this.logger.debug(String.format("Update %s's sql >>> [%s] \n %s's property is %s",
					this.getActualTableName(),
					this.updateSql,
					this.getPojoClass(),
					pojo.toString()));
		}

		this.getSimpleJdbcTemplate().update(this.updateSql, pojo);
	}

	/*
	 * 2010-2-5 Timothy
	 * 
	 * @see com.vs.data.dao.Dao#delete(com.vs.data.pojo.Pojo)
	 */
	public final void delete(P pojo) {

		this.primaryKeyCouldNotEmpty();

		this.getSimpleJdbcTemplate().update(this.deleteSql, pojo);
	}

	/*
	 * 2010-2-5 Timothy
	 * 
	 * @see com.vs.data.dao.Dao#delete(java.io.Serializable)
	 */
	public void delete(PK id) {

		this.primaryKeyCouldNotEmpty();

		P pojo = this.generatePojoInstance();

		pojo.setId(id);

		this.delete(pojo);
	}

	/*
	 * 2010-2-20 Timothy
	 * 
	 * @see com.vs.data.dao.Dao#exists(java.io.Serializable)
	 */
	public boolean exists(PK id) {

		return this.findById(id) != null;
	}

	/*
	 * 2010-2-20 Timothy
	 * 
	 * @see com.vs.data.dao.jdbc.JdbcDao#exists(com.vs.data.pojo.Pojo)
	 */
	public boolean exists(P pojo) {

		return this.findById(pojo) != null;
	}

	/*
	 * 2010-2-5 Timothy
	 * 
	 * @see com.vs.data.dao.Dao#notExists(java.io.Serializable)
	 */
	public boolean notExists(PK id) {

		return !this.exists(id);
	}

	/*
	 * 2010-2-20 Timothy
	 * 
	 * @see com.vs.data.dao.jdbc.JdbcDao#notExists(com.vs.data.pojo.Pojo)
	 */
	public boolean notExists(P pojo) {

		return !this.exists(pojo);
	}

	/*
	 * 2010-3-10 Timothy
	 * 
	 * @see com.vs.data.dao.jdbc.JdbcDao#getPrimaryKeyNextValue()
	 */
	protected PK getPrimaryKeySequenceNextValue() {

		if (!this.autoIncrement) {
			throw new DataException(String.format("Table[%s] 非自动增长的PK序列", this.getActualTableName()));
		}

		String sequenceNextValSql = this
				.getDatabaseType()
					.getSequenceNextValue(this.getPrimaryKeySequence());

		this.logger.debug(String.format("Get %s's PK[%s.NEXTVAL] sql >>> [%s]",
				this.getActualTableName(),
				this.getPrimaryKeySequence(),
				sequenceNextValSql));

		return this.getSimpleJdbcTemplate().queryForObject(sequenceNextValSql, this.pojoPKClass);
	}

	/**
	 * <pre>
	 * 2010-2-20 Timothy
	 * </pre>
	 * 
	 * @return
	 */
	private String generateWhereSqlById() {

		List<String> conditions = new ArrayList<String>();

		for (String primaryKey : this.primaryKey) {
			conditions.add(this.generatePojoFieldEqualsConditionSql(primaryKey));
		}

		return " WHERE " + StringUtils.join(conditions, " AND ");
	}

	/**
	 * <pre>
	 * 2010-2-20 Timothy
	 * 产生此Dao对应的Pojo"RowMapper
	 * </pre>
	 * 
	 * @return
	 */
	protected ParameterizedRowMapper<P> generateParameterizedRowMapper() {

		return new ParameterizedRowMapper<P>() {

			public P mapRow(ResultSet rs, int rowNum) throws SQLException {

				P pojo = AbstractJdbcDao.this.generatePojoInstance();

				ResultSetMetaData metaData = rs.getMetaData();

				// 用于循环取得 column
				for (int i = 1; i <= metaData.getColumnCount(); i++) {

					this.setPojoProperty(pojo, rs, metaData.getColumnName(i));
				}

				return pojo;
			}

			/**
			 * <pre>
			 * 2010-2-23 Timothy
			 * 将栏位"设定到对应的pojo"
			 * </pre>
			 * 
			 * @param pojo
			 * @param rs
			 * @param columnName
			 */
			private void setPojoProperty(P pojo, ResultSet rs, String columnName) {

				String pojoPropertyNameKey = StringUtils.remove(columnName, "_").toLowerCase();

				// 验证 columnName 的确有在pojo中存在对应的属"则进行绑"
				if (AbstractJdbcDao.this.pojoPropertiesNamesIgnoreCaseMap.containsKey(pojoPropertyNameKey)) {

					String pojoProperty = AbstractJdbcDao.this.pojoPropertiesNamesIgnoreCaseMap
							.get(pojoPropertyNameKey);

					try {

						Class<?> pojoPropertyType = PropertyUtils
								.getPropertyDescriptor(pojo, pojoProperty)
									.getPropertyType();
						String pojoPropertyTypeSimpleName = pojoPropertyType.getSimpleName();

						// 以下连续对数值基本类型进行判断取"
						if (StringUtils.equalsIgnoreCase(pojoPropertyTypeSimpleName, "byte")) {
							PropertyUtils.setSimpleProperty(pojo, pojoProperty, rs.getByte(columnName));
						}
						else if (StringUtils.equalsIgnoreCase(pojoPropertyTypeSimpleName, "short")) {
							PropertyUtils.setSimpleProperty(pojo, pojoProperty, rs.getShort(columnName));
						}

						// int 类型的包装类型与基本类型名称不一致，故分"��断！
						else if (StringUtils.equals(pojoPropertyTypeSimpleName, "int")) {
							PropertyUtils.setSimpleProperty(pojo, pojoProperty, rs.getInt(columnName));
						}
						else if (StringUtils.equals(pojoPropertyTypeSimpleName, "Integer")) {

							String str = rs.getString(columnName);

							if (StringUtils.isNotBlank(str)) {

								PropertyUtils.setSimpleProperty(pojo, pojoProperty, rs.getInt(columnName));
							}
							else {

								PropertyUtils.setSimpleProperty(pojo, pojoProperty, null);
							}
						}
						else if (StringUtils.equalsIgnoreCase(pojoPropertyTypeSimpleName, "long")) {

							String str = rs.getString(columnName);

							if (StringUtils.isNotBlank(str)) {

								PropertyUtils.setSimpleProperty(pojo, pojoProperty, rs.getLong(columnName));
							}
							else {

								PropertyUtils.setSimpleProperty(pojo, pojoProperty, null);
							}
						}
						else if (StringUtils.equalsIgnoreCase(pojoPropertyTypeSimpleName, "float")) {
							PropertyUtils.setSimpleProperty(pojo, pojoProperty, rs.getFloat(columnName));
						}
						else if (StringUtils.equalsIgnoreCase(pojoPropertyTypeSimpleName, "double")) {
							PropertyUtils.setSimpleProperty(pojo, pojoProperty, rs.getDouble(columnName));
						}

						// 将所有的 日期栏位"java.sql.Timestamp 的类型取"
						else if (StringUtils.equalsIgnoreCase(pojoPropertyType.getName(), Date.class.getName())) {
							PropertyUtils.setSimpleProperty(pojo, pojoProperty, rs.getTimestamp(columnName));
						}

						// "基本类型 全部使用 Object
						else {

							Object columnContent = rs.getObject(columnName);

							// "��"Clob 类型进行个别处理
							if (columnContent instanceof Clob) {

								PropertyUtils.setSimpleProperty(pojo, pojoProperty, AbstractJdbcDao.this
										.getDatabaseType()
											.convertClobToString((Clob) columnContent));
							}

							// "��"Blob 类型进行个别处理
							else if (columnContent instanceof Blob) {

								PropertyUtils.setSimpleProperty(pojo, pojoProperty, AbstractJdbcDao.this
										.getDatabaseType()
											.convertBlobToBytes((Blob) columnContent));
							}

							else {
								PropertyUtils.setSimpleProperty(pojo, pojoProperty, columnContent);
							}

							// "id 进行特别的判断处理，因为id来自于泛"Serializable
							if (StringUtils.equals(pojoProperty, "id")) {

								if (AbstractJdbcDao.this.pojoPKClass == Integer.class) {
									PropertyUtils.setSimpleProperty(pojo, pojoProperty, rs.getInt(columnName));
								}
								else if (AbstractJdbcDao.this.pojoPKClass == Long.class) {
									PropertyUtils.setSimpleProperty(pojo, pojoProperty, rs.getLong(columnName));
								}
							}
						}
					}
					catch (Exception e) {
						throw new DataException(e);
					}
				}
			}
		};
	}

	/**
	 * <pre>
	 * 2010-2-20 Timothy
	 * </pre>
	 * 
	 * @return
	 */
	private final P generatePojoInstance() {

		P pojo;

		try {
			pojo = this.getPojoClass().newInstance();
		}
		catch (Exception e) {

			throw new DataException("Pojo 必须是可以构造且存在共用默认的构造函数的", e);
		}

		return pojo;
	}

	/*
	 * 2010-2-5 Timothy
	 * 
	 * @see com.vs.data.dao.Dao#getPojoClass()
	 */
	public Class<P> getPojoClass() {

		return this.pojoClass;
	}

	/**
	 * <pre>
	 * 2010-2-23 Timothy
	 * </pre>
	 * 
	 * @return
	 */
	private Map<String, String> getPojoPropertiesNamesIgnoreCaseMap() {

		Map<String, String> pojoPropertiesNamesIgnoreCaseMap = new HashMap<String, String>();

		for (PropertyDescriptor propertyDescriptor : BeanUtils.getPropertyDescriptors(this
				.getPojoClass())) {
			String name = propertyDescriptor.getName();
			pojoPropertiesNamesIgnoreCaseMap.put(name.toLowerCase(), name);
		}

		// 当主键唯"��时"，将主键转小写去下划线后，映射到id上，用于之后其他操作的id的处"
		if (this.getActualPrimaryKey().length == 1) {
			pojoPropertiesNamesIgnoreCaseMap.put(StringUtils
					.remove(this.getActualPrimaryKey()[0], "_")
						.toLowerCase(), "id");
		}

		return pojoPropertiesNamesIgnoreCaseMap;
	}

	/**
	 * <pre>
	 * 2010-2-20 Timothy
	 * </pre>
	 * 
	 * @return
	 */
	private Map<String, String> generateColumnsMapPojoProperty() {

		Map<String, String> columnsMapPojoProperty = new HashMap<String, String>();

		// 处理主键的映射关系：当非符合主键时，固定映射":id
		if (this.getActualPrimaryKey().length == 1) {

			columnsMapPojoProperty.put(this.getActualPrimaryKey()[0], "id");
		}
		else {

			for (String primaryKey : this.getActualPrimaryKey()) {

				String column = StringUtils.remove(primaryKey, "_").toLowerCase();

				if (this.pojoPropertiesNamesIgnoreCaseMap.containsKey(column)) {
					columnsMapPojoProperty.put(primaryKey, this.pojoPropertiesNamesIgnoreCaseMap.get(column));
				}
			}
		}

		// 处理非主键的映射关系
		for (String columnsExpectPrimaryKey : this.getActualTableColumnsExpectPrimaryKey()) {

			String column = StringUtils.remove(columnsExpectPrimaryKey, "_").toLowerCase();

			if (this.pojoPropertiesNamesIgnoreCaseMap.containsKey(column)) {
				columnsMapPojoProperty.put(columnsExpectPrimaryKey,
						this.pojoPropertiesNamesIgnoreCaseMap.get(column));
			}
		}

		return columnsMapPojoProperty;
	}

	/**
	 * <pre>
	 * 2010-2-20 Timothy
	 * </pre>
	 * 
	 * @return
	 */
	private final String[] getActualTableColumnsExpectPrimaryKey() {

		List<String> primaryKeys = new ArrayList<String>(Arrays.asList(this.getActualPrimaryKey()));
		List<String> columns = new ArrayList<String>(Arrays.asList(this.getActualTableColumns()));

		columns.removeAll(primaryKeys);

		return columns.toArray(new String[columns.size()]);
	}

	/**
	 * <pre>
	 * 2010-2-20 Timothy
	 * 设定此Dao对应的真"Table name
	 * </pre>
	 * 
	 * @return
	 */
	protected abstract String getActualTableName();

	/**
	 * <pre>
	 * 2010-2-20 Timothy
	 * 设定此Dao对应的真"Table "PK
	 * </pre>
	 * 
	 * @return
	 */
	protected String[] getActualPrimaryKey() {

		return new String[0];
	}

	/**
	 * <pre>
	 * 2010-2-20 Timothy
	 * 列出table的所有栏位名"
	 * </pre>
	 * 
	 * @return
	 */
	protected abstract String[] getActualTableColumns();

	/**
	 * <pre>
	 * 2010-2-22 Timothy
	 * 只有当对应的DB是ORACLE，且主键为自增序列时，需要在子类中重写此方法返回 sequence name
	 * </pre>
	 * 
	 * @return
	 */
	protected String getActualPrimaryKeySequence() {

		return null;
	}

	/**
	 * <pre>
	 * 2010-3-10 Timothy
	 * </pre>
	 * 
	 * @return
	 */
	private String getPrimaryKeySequence() {

		return StringUtils.defaultIfEmpty(this.getActualPrimaryKeySequence(), this.getActualTableName()
				+ "_SEQ");
	}

	/**
	 * <pre>
	 * 2008/11/17 Timothy
	 * 	查詢 真實的pojo class in assignable dao
	 * </pre>
	 * 
	 * @param daoClass
	 * @return
	 */
	@SuppressWarnings("unchecked")
	private Class<P> findAssignablePojoClass(Class daoClass) {

		try {

			return (Class<P>) VSClassUtils.getGenericSuperClassActualType(daoClass, Pojo.class);
		}
		catch (Exception e) {

			e.printStackTrace();

			throw new DataException(e);
		}
	}

	/**
	 * <pre>
	 * 2008/11/17 Timothy
	 * 	查詢 真實的pojo class in assignable dao
	 * </pre>
	 * 
	 * @param pojoClass
	 * @return
	 */
	@SuppressWarnings("unchecked")
	private Class<PK> findAssignablePojoPKClass(Class pojoClass) {

		try {

			return (Class<PK>) VSClassUtils.getGenericSuperClassActualType(pojoClass, Serializable.class);
		}
		catch (Exception e) {

			e.printStackTrace();

			throw new DataException(e);
		}
	}

	/**
	 * <pre>
	 * 2010-2-20 Timothy
	 * </pre>
	 * 
	 * @return
	 */
	private String generateCreateSqlById() {

		String sql = " INSERT INTO %s(%s, %s) VALUES(%s, %s) ";

		List<String> beanProperties = new ArrayList<String>();
		
		for (String column : this.tableColumns) {
			beanProperties.add(":" + this.columnsMapPojoProperty.get(column));
		}

		return String.format(sql,
				this.getActualTableName(),
				StringUtils.join(this.getActualPrimaryKey(), ", "),
				StringUtils.join(this.tableColumns, ", "),
				":id",
				StringUtils.join(beanProperties, ", "));
	}

	/**
	 * <pre>
	 * 2010-2-20 Timothy
	 * </pre>
	 * 
	 * @param whereSqlById
	 * @return
	 */
	private String generateUpdateSqlById(String whereSqlById) {

		String sql = " UPDATE %s SET %s %s ";

		List<String> setColumns = new ArrayList<String>();

		for (String column : this.tableColumnsExpectPrimaryKey) {
			setColumns.add(String.format("%s = :%s", column, this.columnsMapPojoProperty.get(column)));
		}

		return String.format(sql,
				this.getActualTableName(),
				StringUtils.join(setColumns, ", "),
				whereSqlById);
	}

	/**
	 * <pre>
	 * 2010-2-20 Timothy
	 * </pre>
	 * 
	 */
	private void primaryKeyCouldNotEmpty() {

		if (this.primaryKey.isEmpty()) {
			throw new DataException(new UnsupportedOperationException(String.format("[%s]缺少主键，故不支援此操作,", 
					this.getActualTableName())));
		}
	}

	/**
	 * <pre>
	 * 2010-2-20 Timothy
	 * </pre>
	 * 
	 * @param actualColumns
	 * @param existColumns
	 * @return
	 */
	private List<String> retainColumns(String[] actualColumns, Set<String> existColumns) {

		List<String> actualPrimaryKey = new ArrayList<String>(Arrays.asList(actualColumns));

		actualPrimaryKey.retainAll(existColumns);

		return Collections.unmodifiableList(actualPrimaryKey);
	}

	/**
	 * 
	 * 产生 使用 pojo field 对应"where 条件语句.
	 * 
	 * <pre>
	 * 2010-3-5 Timothy
	 * </pre>
	 * 
	 * @param columnName
	 * @return
	 */
	private String generatePojoFieldEqualsConditionSql(String columnName) {

		return String.format(" %s = :%s ", columnName, this.columnsMapPojoProperty.get(columnName));
	}

	/*
	 * 2010-2-5 Timothy
	 * 
	 * @see org.springframework.jdbc.core.support.JdbcDaoSupport#initTemplateConfig()
	 */
/*	protected void initTemplateConfig() {

		this.simpleJdbcTemplate = new SimpleJdbcTemplate(this.getDataSource());
	}*/

	/**
	 * <pre>
	 * 2010-2-23 Timothy
	 * 	设定为true，标识主键为自动增长
	 * </pre>
	 * 
	 * @param autoIncrement
	 *          设定为true，标识主键为自动增长，default false
	 */
	public void setAutoIncrement(boolean autoIncrement) {

		this.autoIncrement = autoIncrement;

		// 处理主键自增的状"..
		// if (this.autoIncrement) {
		//
		// switch (this.getDatabaseType()) {
		//
		// case ORACLE:
		//
		// // 当只"个主键，"OraclePrimaryKeySequence 不为空时，替":id "对应"Sequence
		// if (this.primaryKey.size() == 1) {
		//
		// this.createSql = this.createSql.replace(":id", this.getPrimaryKeySequence() + ".NEXTVAL");
		// }
		//
		// break;
		// }
		// }
	}

	/**
	 * <pre>
	 * 2010-2-20 Timothy
	 * </pre>
	 * 
	 * @return
	 */
	protected SimpleJdbcTemplate getSimpleJdbcTemplate() {

		return this.simpleJdbcTemplate;
	}

	/**
	 * @return 傳回 databaseType"
	 */
	public DatabaseType getDatabaseType() {

		return DatabaseType.MYSQL;
	}

	/**
	 * @param databaseType
	 *          要設定的 databaseType"
	 */
	public void setDatabaseType(DatabaseType databaseType) {

		this.databaseType = databaseType;
	}

	/**
	 * @return 传回 autoIncrement"
	 */
	public boolean isAutoIncrement() {

		return this.autoIncrement;
	}

	/**
	 * 
	 * logger debug...
	 * 
	 * @param message
	 */
	protected final void debug(String message) {

		if (logger != null && logger.isDebugEnabled()) {
			this.logger.debug(message);
		}
	}

	/**
	 * 
	 * logger info...
	 * 
	 * @param message
	 */
	protected final void info(String message) {

		if (logger != null && logger.isInfoEnabled()) {
			this.logger.info(message);
		}
	}

	/**
	 * 
	 * logger error...
	 * 
	 * @param message
	 */
	protected final void error(String message) {

		if (logger != null && logger.isErrorEnabled()) {
			this.logger.error(message);
		}
	}

	/**
	 * 
	 * logger error...
	 * 
	 * @param message
	 * @param cause
	 */
	protected final void error(String message, Throwable cause) {

		if (logger != null && logger.isErrorEnabled()) {
			this.logger.error(message, cause);
		}
	}
}